CREATE VIEW [vSoaUserRole] AS
WITH Levels (LevelCode) AS (
    SELECT 1 AS [LevelCode]
    UNION
    SELECT 2 AS [LevelCode]
    UNION
    SELECT 3 AS [LevelCode]
    UNION
    SELECT 4 AS [LevelCode]
    UNION
    SELECT 5 AS [LevelCode]
    UNION
    SELECT 6 AS [LevelCode]
    UNION
    SELECT 7 AS [LevelCode]
    UNION
    SELECT 8 AS [LevelCode]
)
SELECT s.[WEB_LOGIN] AS [UserName], 'CustomerLevel' + CAST(r.[LevelCode] AS varchar) AS [Role]
  FROM [dbo].[Users] AS u
       INNER JOIN Levels AS r ON u.[LevelMembership] >= r.[LevelCode]
       INNER JOIN [dbo].[Name_Security] AS s ON s.[WEB_LOGIN] = u.[UserId]
UNION
SELECT s.[WEB_LOGIN] AS [UserName], 'BillingLevel' + CAST(r.[LevelCode] AS varchar) AS [Role]
  FROM [dbo].[Users] AS u
       INNER JOIN Levels AS r ON u.[LevelDues] >= r.[LevelCode]
       INNER JOIN [dbo].[Name_Security] AS s ON s.[WEB_LOGIN] = u.[UserId]
UNION
SELECT s.[WEB_LOGIN] AS [UserName], 'CashARLevel' + CAST(r.[LevelCode] AS varchar) AS [Role]
  FROM [dbo].[Users] AS u
       INNER JOIN Levels AS r ON u.[LevelCashAR] >= r.[LevelCode]
       INNER JOIN [dbo].[Name_Security] AS s ON s.[WEB_LOGIN] = u.[UserId]
UNION
SELECT s.[WEB_LOGIN] AS [UserName], 'EventsLevel' + CAST(r.[LevelCode] AS varchar) AS [Role]
  FROM [dbo].[Users] AS u
       INNER JOIN Levels AS r ON u.[LevelMeeting] >= r.[LevelCode]
       INNER JOIN [dbo].[Name_Security] AS s ON s.[WEB_LOGIN] = u.[UserId]
UNION
SELECT s.[WEB_LOGIN] AS [UserName], 'OrdersLevel' + CAST(r.[LevelCode] AS varchar) AS [Role]
  FROM [dbo].[Users] AS u
       INNER JOIN Levels AS r ON u.[LevelOrderEntry] >= r.[LevelCode]
       INNER JOIN [dbo].[Name_Security] AS s ON s.[WEB_LOGIN] = u.[UserId]
UNION
SELECT s.[WEB_LOGIN] AS [UserName], 'ReferralLevel' + CAST(r.[LevelCode] AS varchar) AS [Role]
  FROM [dbo].[Users] AS u
       INNER JOIN Levels AS r ON u.[LevelReferral] >= r.[LevelCode]
       INNER JOIN [dbo].[Name_Security] AS s ON s.[WEB_LOGIN] = u.[UserId]
UNION
SELECT s.[WEB_LOGIN] AS [UserName], 'CertificationLevel' + CAST(r.[LevelCode] AS varchar) AS [Role]
  FROM [dbo].[Users] AS u
       INNER JOIN Levels AS r ON u.[LevelCert] >= r.[LevelCode]
       INNER JOIN [dbo].[Name_Security] AS s ON s.[WEB_LOGIN] = u.[UserId]
UNION
SELECT s.[WEB_LOGIN] AS [UserName], 'FundraisingLevel' + CAST(r.[LevelCode] AS varchar) AS [Role]
  FROM [dbo].[Users] AS u
       INNER JOIN Levels AS r ON u.[LevelFundRaising] >= r.[LevelCode]
       INNER JOIN [dbo].[Name_Security] AS s ON s.[WEB_LOGIN] = u.[UserId]
UNION
SELECT s.[WEB_LOGIN] AS [UserName], 'ServiceCentralLevel' + CAST(r.[LevelCode] AS varchar) AS [Role]
  FROM [dbo].[Users] AS u
       INNER JOIN Levels AS r ON u.[LevelSC] >= r.[LevelCode]
       INNER JOIN [dbo].[Name_Security] AS s ON s.[WEB_LOGIN] = u.[UserId]
UNION
SELECT s.[WEB_LOGIN] AS [UserName], 'ExpoLevel' + CAST(r.[LevelCode] AS varchar) AS [Role]
  FROM [dbo].[Users] AS u
       INNER JOIN Levels AS r ON u.[LevelExpo] >= r.[LevelCode]
       INNER JOIN [dbo].[Name_Security] AS s ON s.[WEB_LOGIN] = u.[UserId]
UNION
SELECT s.[WEB_LOGIN] AS [UserName], 'SysAdmin' + CAST(r.[LevelCode] AS varchar) AS [Role]
  FROM [dbo].[Users] AS u
       INNER JOIN Levels AS r ON u.[LevelSystem] >= r.[LevelCode]
       INNER JOIN [dbo].[Name_Security] AS s ON s.[WEB_LOGIN] = u.[UserId]
UNION
SELECT s.[WEB_LOGIN] AS [UserName], n.[MEMBER_TYPE] AS [Role]
  FROM [dbo].[Name] n
        INNER JOIN [dbo].[Name_Security] AS s ON n.[ID] = s.[ID]
        INNER JOIN [dbo].[Users] u ON u.[UserId] = s.[WEB_LOGIN]
        WHERE n.[MEMBER_TYPE] <> ''
UNION
SELECT s.[WEB_LOGIN] AS [UserName], 'IsMember' AS [Role]
  FROM [dbo].[Name] n
        INNER JOIN [dbo].[Name_Security] AS s ON n.[ID] = s.[ID]
        INNER JOIN [dbo].[Users] u ON u.[UserId] = s.[WEB_LOGIN]
        WHERE [MEMBER_RECORD] = 1
UNION
SELECT s.[WEB_LOGIN] AS [UserName], 'IsStaff' AS [Role]
  FROM [dbo].[Name] n
        INNER JOIN [dbo].[Name_Security] AS s ON n.[ID] = s.[ID]
        INNER JOIN [dbo].[Users] u ON u.[UserId] = s.[WEB_LOGIN]
UNION
SELECT s.[WEB_LOGIN] AS [UserName], 'AuthenticatedUser' AS [Role]
  FROM [dbo].[Name] n
        INNER JOIN [dbo].[Name_Security] AS s ON n.[ID] = s.[ID]
        INNER JOIN [dbo].[Users] u ON u.[UserId] = s.[WEB_LOGIN]


GO